package com.parse;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.bean.ClassBean;
import com.bean.FieldsBean;
import com.utils.FileUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.beetl.core.Configuration;
import org.beetl.core.GroupTemplate;
import org.beetl.core.Template;
import org.beetl.core.resource.StringTemplateResourceLoader;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;


public class ParseExcel {
	
	/** 红色，后端读取 */
	public static final int BOTH_RED = 10;

	/** 黄色，前端读取 */
	public static final int FRONT_YELLOW =  13;

	/** 白色，都读取 */
	public static final int BACK_WHITE = 64;
	
	private static final String FILTER_SHEETNAME = "$";
	public static String fatherPackage = "com.bean.GameModel";
	
	public static String javabeetlContent ;
	
	public static String egretbeetlContent ;
	

	public static void parse(File file) throws IOException{
		System.out.println("开始解析"+file);
		Workbook wb = FileUtils.readExcel(file.getAbsolutePath());
		//获取页面 个数
		int sheetNum=wb.getNumberOfSheets();
		//只解析第一页
		for(int sheetI=0;sheetI< sheetNum;sheetI++)
		{
			Sheet sheet = wb.getSheetAt(sheetI);
			String sheetName = sheet.getSheetName();
			System.out.println("开始解析页面 "+sheetName);
			if(sheetName.indexOf(FILTER_SHEETNAME)!=-1)
				continue;
			Map<Integer, Integer> colorMap = new LinkedHashMap<Integer, Integer>();
			Map<Integer, String> titleMap = null;
			Map<Integer, String> desMap = null;
			/**key 1行数  key2列数   val 值**/
			Map<Integer, Map<Integer, String>> valuesMap = new LinkedHashMap<Integer, Map<Integer,String>>();
			
			//获取行数
			int rowNum = sheet.getLastRowNum();
			//描述
			Row desRow = sheet.getRow(1);
			//标题
			Row titleRow = sheet.getRow(0);
			//描述
			Row typeRow = sheet.getRow(2);
			
			int cellNum = titleRow.getLastCellNum();
			//获取颜色
			getSheetColor(colorMap, desRow, cellNum);
			//获取标题
			titleMap= getSheetValues(titleRow, cellNum);
			//获取描述
			desMap = getSheetValues(desRow, cellNum);
			//获取类型 
			Map<Integer, String> typeMap = getSheetValues(typeRow, cellNum);
			//解析类型
			Map<Integer, String> clientTypeMap = parseType(typeMap, true);
			Map<Integer, String> serverTypeMap = parseType(typeMap, false);
			for(int i =3;i<=rowNum;i++)
			{
				Row row = sheet.getRow(i);
				valuesMap.put(i, getSheetValuesByType(row, cellNum,typeMap,sheetName));
			}
			toJava(file.getName(),sheet, colorMap, titleMap, desMap, serverTypeMap);
			boolean needParse=false;
			for(int color:colorMap.values())
			{
				if(color==FRONT_YELLOW||color==BACK_WHITE)
				{
					needParse = true;
					break;
				}
			}
			if(!needParse)
				continue;
			toJson(FileUtils.getPrefix(file.getName()), sheet, colorMap, titleMap, valuesMap);
			
			toEgret(file.getName(),sheet, colorMap, titleMap, desMap, clientTypeMap);
			
			//生成XML
//			toXml(sheet, colorMap, titleMap, valuesMap);
//			toAs(sheet, colorMap, titleMap, desMap,clientTypeMap);
			//生成lua
//			toLua(FileUtils.getPrefix(file.getName()), sheet, colorMap, titleMap, valuesMap,typeMap);
		}
	}
	
	
//	private static void toLua(String excelName,Sheet sheet,Map<Integer, Integer> colorMap,Map<Integer, String> titleMap,Map<Integer, Map<Integer, String>> valuesMap,Map<Integer, String> typeMap)
//	{
//		boolean needParse=false;
//		for(int color:colorMap.values())
//		{
//			if(color==FRONT_YELLOW||color==BACK_WHITE)
//			{
//				needParse = true;
//				break;
//			}
//		}
//		if(!needParse)
//			return;
//		String sheetName = sheet.getSheetName();
//		List<LuaExcelBean> fields = new ArrayList<LuaExcelBean>();
//		for(Map<Integer, String> row:valuesMap.values())
//		{
//			LuaExcelBean bean =new LuaExcelBean();
//			for(Map.Entry<Integer, String> field:row.entrySet())
//			{
//				if(colorMap.get(field.getKey())!=FRONT_YELLOW&&colorMap.get(field.getKey())!=BACK_WHITE)
//					continue;
//				LuaExcelFields fieldObj = new LuaExcelFields();
//				fieldObj.name = titleMap.get(field.getKey());
//				fieldObj.values = field.getValue();
//				fieldObj.type = typeMap.get(field.getKey());
//				
//				if(fieldObj.name.equals("id"))
//				{
//					bean.setKey(fieldObj.values);
//					continue;
//				}
//				bean.fields.add(fieldObj);
//			}
//			fields.add(bean);
//		}
//		VelocityContext _context = new VelocityContext();
//		_context.put("sheetName", "XLS_"+sheetName+"_"+excelName);
//		_context.put("rows", fields);
//		StringWriter _readWriter = new StringWriter();
//		try {
//			Velocity.mergeTemplate("ClientClass.vm",
//					"UTF-8", _context, _readWriter);
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
//		String tmpPath = System.getProperty("user.dir") + File.separator
//				+ "lua" + File.separator + "XLS_"+sheetName+"_"+excelName + ".lua";
//		FileUtils.writeStringFile(_readWriter.toString(), tmpPath, "UTF-8");
//	}
	
	
	
	/**
	 * 生成xml
	 */
	private static void toXml(Sheet sheet,Map<Integer, Integer> colorMap,Map<Integer, String> titleMap,Map<Integer, Map<Integer, String>> valuesMap)
	{
		StringBuffer clientBuffer = new StringBuffer();
		clientBuffer.append("<"+sheet.getSheetName()+">\n");
		for(Map<Integer, String> values :valuesMap.values())
		{
			clientBuffer.append("<i ");
			for(int index:colorMap.keySet())
			{
				int color = colorMap.get(index);
				String title = titleMap.get(index);
				if(color!=FRONT_YELLOW&&color!=BACK_WHITE)
					continue;
				clientBuffer.append(title+"=\""+values.get(index)+"\" ");
			}
			clientBuffer.append(" />\n");
		}
		clientBuffer.append("</"+sheet.getSheetName()+">");
		writeFileToXML(clientBuffer.toString(), sheet.getSheetName());
	}
	
	private static void toEgret(String excelName,Sheet sheet,Map<Integer, Integer> colorMap,Map<Integer, String> titleMap,Map<Integer, String> desMap,Map<Integer, String> typeMap) throws IOException
	{
		boolean needParse=false;
		for(int color:colorMap.values())
		{
			if(color==FRONT_YELLOW||color==BACK_WHITE)
			{
				needParse = true;
				break;
			}
		}
		if(!needParse)
			return;
		String sheetName = sheet.getSheetName();
		ClassBean classBean = new ClassBean();
		List<FieldsBean> fields = new ArrayList<FieldsBean>();
		for(Map.Entry<Integer, String> entry:titleMap.entrySet())
		{
			if(colorMap.get(entry.getKey())!=BOTH_RED&&colorMap.get(entry.getKey())!=BACK_WHITE)
				continue;
			FieldsBean field = new FieldsBean();
			field.desc = desMap.get(entry.getKey());
			field.type = typeMap.get(entry.getKey());
			if(field.type.toLowerCase().equals("int"))
			{
			    field.type = "number";
			}
			else
			{
			    field.type = field.type.toLowerCase();
			}
			field.name = entry.getValue();
			field.bigName = entry.getValue();
			field.bigName= field.bigName.substring(0, 1).toUpperCase()
					+ field.bigName.substring(1);
			fields.add(field);
		}
		classBean.setFieldList(fields);
		classBean.desc = excelName;
		classBean.name = "CofData"+FileUtils.getPrefix(excelName) +"_"+sheetName;
		classBean.fileName = excelName+"_"+sheet.getSheetName();
		
		StringTemplateResourceLoader resourceLoader = new StringTemplateResourceLoader();
		Configuration cfg = Configuration.defaultConfiguration();
		GroupTemplate gt = new GroupTemplate(resourceLoader, cfg);
		Template t = gt.getTemplate(egretbeetlContent);
		t.binding("class",classBean);
		
		String content = t.render();
		String tmpPath = System.getProperty("user.dir") + File.separator
				+ "egret" + File.separator + "CofData"+FileUtils.getPrefix(excelName) +"_"+sheetName+ ".ts";
		FileUtils.writeStringFile(content, tmpPath, "UTF-8");
	}
	
	
	private static void toJava(String excelName,Sheet sheet,Map<Integer, Integer> colorMap,Map<Integer, String> titleMap,Map<Integer, String> desMap,Map<Integer, String> typeMap) throws IOException
	{
		boolean needParse=false;
		for(int color:colorMap.values())
		{
			if(color==BOTH_RED||color==BACK_WHITE)
			{
				needParse = true;
				break;
			}
		}
		if(!needParse)
			return;
		String sheetName = sheet.getSheetName();
		ClassBean classBean = new ClassBean();
		List<FieldsBean> fields = new ArrayList<FieldsBean>();
		for(Map.Entry<Integer, String> entry:titleMap.entrySet())
		{
			if(colorMap.get(entry.getKey())!=BOTH_RED&&colorMap.get(entry.getKey())!=BACK_WHITE)
				continue;
			FieldsBean field = new FieldsBean();
			field.desc = desMap.get(entry.getKey());
			field.type = typeMap.get(entry.getKey());
			field.name = entry.getValue();
			field.bigName = entry.getValue();
			field.bigName= field.bigName.substring(0, 1).toUpperCase()
					+ field.bigName.substring(1);
			fields.add(field);
		}
		classBean.setFieldList(fields);
		classBean.desc = excelName;
		classBean.name = "XLS"+FileUtils.getPrefix(excelName) +"_"+sheetName+"Config";
		classBean.fatherName = "GameModel";
		classBean.fatherPackage = fatherPackage;
		classBean.fileName = excelName+"_"+sheet.getSheetName();
		
		
		
		StringTemplateResourceLoader resourceLoader = new StringTemplateResourceLoader();
		Configuration cfg = Configuration.defaultConfiguration();
		GroupTemplate gt = new GroupTemplate(resourceLoader, cfg);
		Template t = gt.getTemplate(javabeetlContent);
		t.binding("class",classBean);
		
		String content = t.render();
		String tmpPath = System.getProperty("user.dir") + File.separator
				+ "java" + File.separator + "XLS"+FileUtils.getPrefix(excelName) +"_"+sheetName+ "Config.java";
		FileUtils.writeStringFile(content, tmpPath, "UTF-8");
	}
	
	
	
	
	/**
	 * 生成as
	 */
	private static void toAs(Sheet sheet,Map<Integer, Integer> colorMap,Map<Integer, String> titleMap,Map<Integer, String> desMap,Map<Integer, String> typeMap)
	{
		//生成as文件
		StringBuffer asBuffer = new StringBuffer();
		asBuffer.append("package{\n");
		asBuffer.append("public class "+sheet.getSheetName()+"{");
		for(int index:colorMap.keySet())
		{
			int color = colorMap.get(index);
			String title = titleMap.get(index);
			String des = desMap.get(index);
			String type = typeMap.get(index);
			if(color!=FRONT_YELLOW&&color!=BACK_WHITE)
				continue;
			asBuffer.append("/**"+des+"**/\n");
			asBuffer.append("public var "+title+":"+type+";\n");
		}
		asBuffer.append("public function parseXml(xml:XML):void{\n");
		for(int index:colorMap.keySet())
		{
			int color = colorMap.get(index);
			String title = titleMap.get(index);
			if(color!=FRONT_YELLOW&&color!=BACK_WHITE)
				continue;
			asBuffer.append("this."+title+"=xml.@"+title+";\n");
		}
		asBuffer.append("}\n");
		asBuffer.append("}\n}\n");
		writeFileToAS(asBuffer.toString(), sheet.getSheetName());
	}
	
	
	
	public static void toJson(String excelName,Sheet sheet,Map<Integer, Integer> colorMap,Map<Integer, String> titleMap,Map<Integer, Map<Integer, String>> valuesMap)
	{
		boolean needParse=false;
		for(int color:colorMap.values())
		{
			if(color==FRONT_YELLOW||color==BACK_WHITE)
			{
				needParse = true;
				break;
			}
		}
		if(!needParse)
			return;
		if(valuesMap.isEmpty())
			return;
		String sheetName = sheet.getSheetName();
		JSONArray jsonArray = new JSONArray();
		for(Map<Integer, String> row:valuesMap.values())
		{
			JSONObject jsonObject = new JSONObject();
			for(Map.Entry<Integer, String> field:row.entrySet())
			{
				if(colorMap.get(field.getKey())!=FRONT_YELLOW&&colorMap.get(field.getKey())!=BACK_WHITE)
					continue;
				String name = titleMap.get(field.getKey());
				String values = field.getValue();
				jsonObject.put(name, values);
			}
			jsonArray.add(jsonObject);
		}
		String tmpPath = System.getProperty("user.dir") + File.separator
		+ "json" + File.separator + excelName+"_"+sheetName + ".json";
		FileUtils.writeStringFile(jsonArray.toJSONString(), tmpPath, "UTF-8");
	}
	
	
	public static void writeFileToAS(String str, String xlsName)
	{
		xlsName = xlsName.replaceAll("&", "/");
		String tmpPath = System.getProperty("user.dir") + File.separator
				+ "as" + File.separator + xlsName + ".as";
		FileUtils.writeStringFile(str, tmpPath, "UTF-8");
		System.out.println(" create as file name:( " + xlsName + " )success ");
	
	}
	
	public static void writeFileToJava(String str, String xlsName)
	{
		xlsName = xlsName.replaceAll("&", "/");
		String tmpPath = System.getProperty("user.dir") + File.separator
				+ "java" + File.separator + xlsName + ".java";
		FileUtils.writeStringFile(str, tmpPath, "UTF-8");
		System.out.println(" create java file name:( " + xlsName + " )success ");
	}
	
	
	/**
	 * 前台生成XML文件
	 * 
	 * @param str
	 */
	public static void writeFileToXML(String str, String xlsName)
	{
		xlsName = xlsName.replaceAll("&", "/");
		String tmpPath = System.getProperty("user.dir") + File.separator
				+ "xml" + File.separator + xlsName + ".xml";
		FileUtils.writeStringFile(str, tmpPath, "UTF-8");
		System.out.println(" create XML file name:( " + xlsName + " )success ");
	}
	
	/**
	 * 获取一行的值
	 * @param colorRow
	 * @param cellNum
	 * @return
	 */
	private static Map<Integer, String> getSheetValues(Row row,int cellNum)
	{
		Map<Integer, String> title = new LinkedHashMap<Integer, String>();
		for(int i = 0 ;i<cellNum;i++)
		{
			Cell cell=row.getCell(i);
			title.put(i, getValues(cell));
		}
		return title;
	}
	
	private static Map<Integer, String> getSheetValuesByType(Row row,int cellNum,Map<Integer, String> typeMap,String sheetName)
	{
		
		Map<Integer, String> title = new LinkedHashMap<Integer, String>();
		for(int i = 0 ;i<cellNum;i++)
		{
			Cell cell=row.getCell(i);
			String type = typeMap.get(i);
			title.put(i, getValues(cell,type,sheetName,row.getRowNum(),i));
		}
		return title;
	}
	
	
	
	/**
	 * 获取一个页面的颜色
	 * @param colorMap
	 * @param colorRow
	 * @param cellNum
	 */
	private static void getSheetColor(Map<Integer, Integer> colorMap,Row colorRow,int cellNum)
	{
		for(int i = 0 ;i<cellNum;i++)
		{
			Cell cell=colorRow.getCell(i);
			int co = cell.getCellStyle().getFillForegroundColor();
//			System.out.println();
//			Color color = cell.getCellStyle().getFillForegroundColorColor();
//			ColorInfo ci = null;  
//			if (color instanceof XSSFColor) {// .xlsx  
//		        XSSFColor xc = (XSSFColor) color;  
//		        byte[] b = xc.getRgb();  
//		        if (b != null) {// 一定是argb  
//		            ci = ColorInfo.fromARGB(b[0], b[1], b[2]);  
//		        }  
//		    } else if (color instanceof HSSFColor) {// .xls  
//		        HSSFColor hc = (HSSFColor) color;  
//		        short[] s = hc.getTriplet();// 一定是rgb  
//		        if (s != null) {  
//		            ci = ColorInfo.fromARGB(s[0], s[1], s[2]);  
//		        }  
//		    }  
			colorMap.put(i, co);
		}
	}
	
	
	/**
	 * 解析类型
	 * @return
	 */
	private static Map<Integer, String> parseType(Map<Integer, String> typeMap,boolean client)
	{
		Map<Integer, String> trueTypeMap = new LinkedHashMap<Integer, String>();
		
		for(Map.Entry<Integer, String> entry:typeMap.entrySet())
		{
			String valuse = entry.getValue().trim();
			valuse=valuse.toLowerCase();
			if(valuse.equals("int"))
			{
				trueTypeMap.put(entry.getKey(), "int");
			}
			else if(valuse.equals("bool"))
			{
				trueTypeMap.put(entry.getKey(), "int");
			}
			else if(valuse.equals("string"))
			{
				trueTypeMap.put(entry.getKey(), "String");
			}
			else if(valuse.equals("float"))
			{
				trueTypeMap.put(entry.getKey(), "int");
			}
			else if(valuse.equals("boolean"))
			{
				trueTypeMap.put(entry.getKey(), "boolean");
			}
			else
			{
				trueTypeMap.put(entry.getKey(), "int");
			}
		}
		return trueTypeMap;
	}
	

	
	
	
	
	public static String getValues(Cell cell,String type,String sheetName,int rowNum,int index)
	{
		try {
			if(type.equals("int"))
			{
				if(cell==null)
					return "0";
				cell.setCellType(Cell.CELL_TYPE_STRING);
				String temp = cell.getStringCellValue().trim();
				if(temp.equals(""))
					return "0";
				double values = Double.parseDouble(temp);
				if(values>0)
				{
					return String.valueOf((int)Math.ceil(values));
				}else
				{
					return String.valueOf((int)Math.floor(values));
				}
			}
			
			if(cell==null)
				return "";
			
			if(type.equals("String")||type.equals("string"))
			{
				cell.setCellType(Cell.CELL_TYPE_STRING);
				return cell.getStringCellValue();
			}
			return "";
		} catch (Exception e) {
			System.out.println("解析excel的sheet"+sheetName+"  行"+rowNum+"  列"+index+"   错误");
			throw new RuntimeException(e);
		}
	}
	
	
	public static String getValues(Cell cell)
	{
		if(cell==null)
			return "";
		int type = cell.getCellType();
		if(type ==Cell.CELL_TYPE_NUMERIC)
		{
			double values = cell.getNumericCellValue();
			if(values>0)
			{
				return String.valueOf((int)Math.ceil(values));
			}else
			{
				return String.valueOf((int)Math.floor(values));
			}
		}
		if(type==Cell.CELL_TYPE_STRING)
			return cell.getStringCellValue();
		return "";
	}
	
	
	
	
	public static void main(String[] args) {
//		parse("D:/task.xls");
		String a = "true";
		Boolean b = Boolean.getBoolean(a);
		System.out.println(b.booleanValue());
	}
}
